declare
    cursor pids is select distinct(pid) from source_affected_items where sync_state = 0  and sites is not null order by pid ;
    cursor ais(current_pid number) is select item_number,sites,pid from source_affected_items where sync_state = 0  and pid = current_pid and sites is not null;

    current_ai_number source_affected_items.item_number%type;
    --ai site
    current_ai_site source_affected_items.sites%type;
    --ai_bom_timestamp
    ai_bom_timestamp source_bom.timestamp%type;
    ai_bom_child_no varchar(200);
    --need to be updated
    current_pid number;
    ai_pid number;
    ai_count number;
    ai_bom_count number;
begin
   open pids;
   loop
      fetch pids into current_pid;
   exit when pids%notfound;
        open ais(current_pid);
        loop
            fetch ais into current_ai_number, current_ai_site, ai_pid;
        exit when ais%notfound;
            select count(1) into ai_bom_count from source_bom where pid = ai_pid and item_number = current_ai_number and site_name = current_ai_site;
            --dbms_output.put_line('no:' || current_ai_number || ',site:' || current_ai_site || ',pid:' || ai_pid || ',count:' || ai_bom_count);
            if ai_bom_count > 0 then
                update source_bom a set sync_state = 0 where a.timestamp in (
                select b.timestamp
                from source_bom b
                where pid = ai_pid
                start with item_number = current_ai_number and site_name = current_ai_site
                connect by item_number = prior child_item_number and site_name = current_ai_site
              );
            end if;
        end loop;
        close ais;
        proc_sys_writelog(2, 1, 'update sync_state', 'pid: ' || current_pid, '');
        commit;
    end loop;
    close pids;
end;
/